package com.aier.school.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import net.eicp.ghostboy.futil.MD5Util;
import net.eicp.ghostboy.futil.RandomUtil;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.classes.common.InviteRequestUtil;
import com.aier.classes.entity.ClassTeacher;
import com.aier.classes.entity.InviteTeacher;
import com.aier.classes.entity.mapper.ClassTeacherRowMapper;
import com.aier.classes.entity.mapper.InviteTeacherRowMapper;
import com.aier.classes.service.IClassUserService;
import com.aier.classes.service.IInviteRequestService;
import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.common.service.ISysPhoneMessageService;
import com.aier.school.entity.SchoolTeacher;
import com.aier.school.entity.mapper.AssignMessageRecordRowMapper;
import com.aier.school.entity.mapper.SchoolTeacherRowMapper;
import com.aier.school.service.ISchoolTeacherService;
import com.aier.user.entity.User;
import com.aier.user.service.IUserService;

@Component("schoolTeacherService")
public class SchoolTeacherServiceImpl_J implements ISchoolTeacherService{
    @Resource(name = "commonDao")
    private ICommonDAO commonDao;
    @Resource(name = "inviteRequestService")
    private IInviteRequestService inviteRequestService; 
    @Resource(name = "userService")
    private IUserService userService;
    @Resource(name = "classUserService")
    private IClassUserService classUserService;
    @Resource(name = "sysPhoneMessageService")
    private ISysPhoneMessageService sysPhoneMessageService;
    
    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    public void setInviteRequestService(IInviteRequestService inviteRequestService) {
        this.inviteRequestService = inviteRequestService;
    }
    public void setUserService(IUserService userService) {
        this.userService = userService;
    }
    public void setClassUserService(IClassUserService classUserService) {
        this.classUserService = classUserService;
    }
    public void setSysPhoneMessageService(
            ISysPhoneMessageService sysPhoneMessageService) {
        this.sysPhoneMessageService = sysPhoneMessageService;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchTNameAndAccountBySid(Integer sid) {
        return (List<String>) commonDao
                .list("select t.`tname` as name,u.account as account from school_teacher t " +
                        "left join `user` as u on t.uid=u.uid where t.sid=?",
                        new Object[] {sid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("name")+" "+rs.getString("account");
                            }
                        });
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchTNameAndAccountBySidGid(Long gid, Integer sid){
        return (List<String>) commonDao
                .list("select t.`tname` as name,u.account as account from school_teacher t " +
                        "left join `user` as u on t.uid=u.uid where t.sid=? and t.gid=?",
                        new Object[] {sid,gid},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int arg1)
                                throws SQLException {
                                return rs.getString("name")+" "+rs.getString("account");
                            }
                        });        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> selectTeacher(Integer sid,String taccount,Long gid) {
        StringBuffer sql = new StringBuffer(
                "select st.* from school_teacher st where st.sid="
                        + sid);
        if (taccount != null && !taccount.equals("")) {
            sql.append(" and st.uid=(select uid from user u where u.account='"
                    + taccount + "')");
        }
        if (null != gid && -1 != gid) {
            sql.append(" and st.gid=" + gid);
        }

        List<SchoolTeacher> tList = null;
        try {
            tList = (List<SchoolTeacher>) commonDao.list(sql.toString(),new SchoolTeacherRowMapper());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tList;
    }
    
    @Override
    public Map<String, Object> searchSchoolTeacherList(int page, int pagesize,
            Integer sid,String account,Long gid) {
        String sql = " from school_teacher st where st.sid=? ";
        if (account != null && !account.equals("")) {
            sql = sql + (" and st.uid=(select uid from user u where u.account='"
                    + account + "')");
        }
        if (null != gid && -1 != gid) {
            sql = sql + (" and st.gid=" + gid);
        }
        String sql_first = sql;
        return commonDao.queryPage(
            "select st.*,(select account from user where uid=st.uid) as account,(select sex from user where uid=st.uid) as sex," +
            "(select name from school_grade where gid=st.gid) as gname," +
            "(select group_concat(cname) from classes where cid in (select cid from class_teacher where tid=st.tid)) as classNams_in," +
            "(select group_concat(cname) from classes where cid in (select cid from class_teacher where tid=st.tid and isadmin=1)) " +
            "as className_manage " + sql, "select count(*) " + sql_first, page,
            pagesize, new Object[] { sid }, new Object[] { sid },
            new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_CLASSTNAME));  
    }
    
    
    @Override
    @SuppressWarnings("unchecked")
    public SchoolTeacher searchSchoolTeacherById(Long tid){
        if(tid==null){
            return null;
        }
        SchoolTeacher schoolTeacher = (SchoolTeacher) commonDao.listByObject(
                "select * from school_teacher where tid=?", new Object[] { tid }, new SchoolTeacherRowMapper());
        List<ClassTeacher> classTeacherList = (List<ClassTeacher>) commonDao.list(
                "select * from class_teacher where tid=?", new Object[] { tid }, new ClassTeacherRowMapper());
        schoolTeacher.setClassTeacherList(classTeacherList);
        return schoolTeacher;
    }
    
    @Override
    public SchoolTeacher searchSchoolTeacherByAccount(String account,Integer sid){
        SchoolTeacher schoolTeacher = (SchoolTeacher) commonDao.listByObject(
                "select * from school_teacher where uid=(select uid from user where account=?) and sid=?", 
                new Object[] { account,sid }, new SchoolTeacherRowMapper());
        return schoolTeacher;       
    }
    
    @Override
    public SchoolTeacher searchSchoolTeacherByIds(Long uid, Integer sid){
        String sql = "select * from school_teacher where uid=?";
        if(sid!=null){
            sql += " and sid=" + sid;
        }
        sql += " limit 1";
        SchoolTeacher schoolTeacher = (SchoolTeacher) commonDao.listByObject(
                sql, new Object[] { uid }, new SchoolTeacherRowMapper());
        return schoolTeacher;
    }
    
    @Override
    public int insertSchoolTeacher(SchoolTeacher sTeacher) {
        KeyHolder kh = commonDao.insert(
                SchoolTeacherRowMapper.getPreparedStatementCreator(sTeacher),
                new GeneratedKeyHolder());
        if(kh!=null){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteSchoolTeacher(Long tid) {
        if (commonDao.update("delete from school_teacher where tid=?",
                new Object[] {tid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolTeacherGrade(Long tid, Long gid) {
        if (commonDao.update("update school_teacher set gid=? where tid=?",
                new Object[] { gid,tid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolTeacherState(Long tid, Short isadmin) {
        if (commonDao.update("update school_teacher set isadmin=? where tid=?",
                new Object[] { isadmin,tid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolTeacherManager(Long tid, Long gid, String cidList,
            String cidList_isadmin) {
        Timestamp ctime = new Timestamp(new Date().getTime());
        if(gid==-1){
            gid = null;
        }
        updateSchoolTeacherGrade(tid, gid);
        commonDao.update("delete from class_teacher where tid=?",new Object[] { tid});
        if(cidList!=null&&!"".equals(cidList)){
            for(String cid:cidList.split(",")){
                if(!cid.equals("")){
                    commonDao.update("insert into class_teacher(tid,cid,ctime) values(?,?,?)",
                            new Object[] { tid,Long.valueOf(cid),ctime});
                }
            }
        }
        if(cidList_isadmin!=null&&!"".equals(cidList_isadmin)){
            for(String cid:cidList_isadmin.split(",")){
                if(!cid.equals("")){
                    commonDao.update("update class_teacher set isadmin=1 where tid=? and cid=?",
                            new Object[] { tid,Long.valueOf(cid)});
                }
            }
        }
        
        return 1;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> searchSchoolTeacherList(Integer sid){
        return (List<SchoolTeacher>) commonDao.list("select st.*,(select desinfo from user where uid=st.uid) as desinfo," +
                 "(select headimg from user where uid=st.uid) as headimg,(select name from school_grade where gid=st.gid) as gname," +
                 "(select group_concat(cname) from classes where cid in (select cid from class_teacher where tid=st.tid)) as classNams_in " +
                 " from school_teacher st where st.sid=?" , new Object[] {sid}, new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_TEACHERINFO));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> searchSchoolTeacherListSid_account(Integer sid){
        return (List<SchoolTeacher>) commonDao.list("select st.*,(select account from user where uid=st.uid) as account" + 
                " from school_teacher st where st.sid=?" , new Object[] {sid}, 
                new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_TEACHERACCOUNT));        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> searchSchoolTeacherListTidList_account(List<Long> tidList){
        String temp_tids = "(";
        for(int i=0;i<tidList.size();i++){
            if(i==tidList.size()-1){
                temp_tids = temp_tids + tidList.get(i) + ")";
            }else{
                temp_tids = temp_tids + tidList.get(i) + ",";
            }
        }
        String sql = "select st.*,(select account from user where uid=st.uid) as account  from school_teacher st where st.tid in " + temp_tids;
        return (List<SchoolTeacher>) commonDao.list(sql, new Object[] {}, 
                new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_TEACHERACCOUNT));         
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacher> searchSchoolTeacherListCidList_account(List<Long> cidList){
        String temp_cids = "(";
        for(int i=0;i<cidList.size();i++){
            if(i==cidList.size()-1){
                temp_cids = temp_cids + cidList.get(i) + ")";
            }else{
                temp_cids = temp_cids + cidList.get(i) + ",";
            }
        }
        String sql = "select st.*,(select account from user where uid=st.uid) as account  from school_teacher st where st.tid in " +
                "(select tid from class_teacher where cid in " + temp_cids + ")";
        return (List<SchoolTeacher>) commonDao.list(sql, new Object[] {}, 
                new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_TEACHERACCOUNT));         
    }


    @Override
    public int updateTeacherMessage(Long tid, Integer mesCount,Integer sid) {
        commonDao.update("INSERT assign_message_record(tid,count,adate) value(?,?,?)",
                new Object[] {tid,mesCount,new Timestamp(new Date().getTime())});
        
        if (commonDao.update("UPDATE school_teacher st SET st.mesCount = IFNULL(st.mesCount,0)+? WHERE st.tid=?",
                new Object[] {mesCount,tid})>0) {
            commonDao.update("UPDATE school s SET s.mesCount = s.mesCount-? WHERE s.sid=?",
                    new Object[] {mesCount,sid});
            return 1;
        } else {
            return 0;
        }
        
    }


    @Override
    public Map<String, Object> searchAssignList(Long tid, int page,
            int page_size) {
        String sql = "select a.*,s.tname from assign_message_record a left join school_teacher s on s.tid= a.tid where a.tid=? order by a.adate desc";
        
        return commonDao.queryPage(sql,
                    page, page_size, new Object[]{tid},
                     new AssignMessageRecordRowMapper());
    }
    
    @Override
    public Map<String, Object> searchInviteTeacherList(int page, int pagesize,
            Integer sid) {
        String sql = " from invite_teacher it where sid=? ";
        
        sql = sql + " order by cid,state,ctime desc";
        String sql_first = sql;
        return commonDao.queryPage(
            "select *,(select name from school_grade where gid=it.gid) as gname,(select cname from classes where cid = it.cid) as cname " + sql, 
            "select count(*) " + sql_first, page,
            pagesize, new Object[] { sid, }, new Object[] { sid },
            new InviteTeacherRowMapper(InviteTeacherRowMapper.AND_GNAME));  
    }
    
    @Override
    public int insertTeacherList(Integer sid,Long tid,
            String inviteTea_message){
        int s = 1;
        Timestamp ctime = new Timestamp(new Date().getTime());
        for(String strtmp:inviteTea_message.split("@")){    //tname,gid,phone
            if(!"".equals(strtmp)){
                // 封装数据，插入添加记录表中
                String[] strList = strtmp.split(",");
                Long cid = Long.valueOf(strList[0]);
                String tname = strList[1];
                Long gid = Long.valueOf(strList[2]);
                if(-1L==cid){
                    cid = null;
                }
                if(-1L==gid){
                    gid = null;
                }
                String phone = strList[Constant.num_3];
                InviteTeacher inviteTeacher = new InviteTeacher(null, tname, phone, gid, sid, cid, tid, null, ctime);
                KeyHolder kh = commonDao.insert(
                        InviteTeacherRowMapper.getPreparedStatementCreator(inviteTeacher),
                        new GeneratedKeyHolder());
                if(kh!=null){
                    
                    InviteTeacher it = inviteRequestService.searchInviteTeacherById(kh.getKey().longValue());
                    //没有教师账号则新建一个账号
                    User user_teacher = userService.searchUserByCondition(it.getPhone());
                    String pwd = "";
                    if(user_teacher==null){
                        pwd = RandomUtil.genRandomNum(Constant.num_6);
                        user_teacher = new User(it.getPhone(), MD5Util.getMD5(pwd), it.getTname(), it.getPhone());
                        userService.insertUserNormal(user_teacher);
                        user_teacher = userService.searchUserByCondition(it.getPhone());
                    }
                    SchoolTeacher schoolTeacher = (SchoolTeacher) commonDao.listByObject("select * from school_teacher where uid=? and sid=?", 
                            new Object[] {user_teacher.getUid(),it.getSid()},new SchoolTeacherRowMapper());
                    if(schoolTeacher==null){
                        if(gid!=null&&gid!=0){
                            schoolTeacher = new SchoolTeacher(null, user_teacher.getUid(), it.getSid(), it.getTname(),gid , ctime,null);
                        }else{
                            schoolTeacher = new SchoolTeacher(null, user_teacher.getUid(), it.getSid(), it.getTname(),null , ctime,null);
                        }
                        KeyHolder kh1 = commonDao.insert(SchoolTeacherRowMapper.getPreparedStatementCreator(schoolTeacher),
                                new GeneratedKeyHolder());
                        schoolTeacher.setTid(kh1.getKey().longValue());
                    }
                    if(it.getCid()!=null&&it.getCid()!=0){
                        int class_num = commonDao.countByQuali(" from class_teacher where tid=? and cid=?", 
                                new Object[] {schoolTeacher.getTid(), it.getCid() });
                        if(class_num==0){
                            ClassTeacher classTeacher = new ClassTeacher(null, schoolTeacher.getTid(), it.getCid(), ctime, null);
                            classUserService.insertClassTeacher(classTeacher);
                        }
                    }
                    
                    //发送短信
                    inviteTeacher = inviteRequestService.searchInviteTeacherById(kh.getKey().longValue());
                    String phMessage = InviteRequestUtil.getAddTeacherMessage(inviteTeacher.getName_teacher(), 
                            inviteTeacher.getName_school(), inviteTeacher.getName_class(),user_teacher.getAccount(),pwd);
                    //sysPhoneMessageService.insertSendPhoneMessage(inviteTeacher.getPhone(), phMessage);
                    sysPhoneMessageService.insertSchoolPhoneMessageMobile(inviteTeacher.getPhone(), phMessage,null,null,0,null);
                    s = 1;
                }else{
                    s = 0;
                }
                
                if(s==0){
                    return 0;
                }
            }
        }
        return s;
    }
    
    @Override
    public int updateMessage(Integer sid, String assignList) {
        int s = 1;
        Timestamp ctime = new Timestamp(new Date().getTime());
        
        for(String assign:assignList.split("@")){
            if(!"".equals(assign)){
                // 封装数据，插入添加记录表中
                String[] strList = assign.split(",");
                String cid = strList[0];
                String tid = strList[1];
                int mesCount = Integer.valueOf(strList[2]);
                if (null != cid && !"".equals(cid)) {
                    if (null != tid && !"".equals(tid)) {
                        commonDao.update("INSERT assign_message_record(sid,tid,count,adate) value(?,?,?,?)",
                                new Object[] {sid,tid,mesCount,ctime});
                        commonDao.update("UPDATE school_teacher st SET st.mesCount = IFNULL(st.mesCount,0)+? WHERE st.tid=?",
                                new Object[] {mesCount,tid});
                        commonDao.update("UPDATE school s SET s.mesCount = s.mesCount-? WHERE s.sid=?",
                                new Object[] {mesCount,sid});
                    } else {
                        commonDao.update("INSERT assign_message_record(sid,cid,count,adate) value(?,?,?,?)",
                                new Object[] {sid,cid,mesCount,ctime});
                        commonDao.update("UPDATE classes c SET c.mesCount = IFNULL(c.mesCount,0)+? WHERE c.cid=?",
                                new Object[] {mesCount,cid});
                        commonDao.update("UPDATE school s SET s.mesCount = s.mesCount-? WHERE s.sid=?",
                                new Object[] {mesCount,sid});
                    }
                }
            }
        }
        return s;
    }
    
    @Override
    public Map<String, Object> searchAssignRecord(Integer sid, Long cid, int page,
            int page_size) {
        if (null == cid) {
            String sql = "select a.*,st.tname,c.cname from assign_message_record a left join school_teacher st on a.tid = st.tid " +
                    "left join classes c on a.cid = c.cid where a.sid = ? order by a.adate desc";
            return commonDao.queryPage(sql,
                    page, page_size, new Object[]{sid},
                     new AssignMessageRecordRowMapper());
        } else {
            String sql = "select a.*,st.tname,c.cname from assign_message_record a left join school_teacher st on a.tid = st.tid " +
                    "left join classes c on a.cid = c.cid where a.cid = ? order by a.adate desc";
            return commonDao.queryPage(sql,
                    page, page_size, new Object[]{cid},
                     new AssignMessageRecordRowMapper());
        }
        
        
        
    }
	@Override
	public Map<String, Object> searchSchoolTeacherNew(Integer sid, Long stid ,Long gid , int page,
			int page_size) {
		Map<String, Object> map =new HashMap<String, Object>();
		if(sid !=null ){
			 String sql =" from school_teacher st where st.sid=?  ";
			
			 if (null != gid && -1 != gid) {
		         sql = sql + (" and st.gid=" + gid);
		     }
			 
		     String sql_first = sql;
		     
		    String newsql= "select st.*,(select phone from user where uid=st.uid) as phone , " +
	         " (select name from school_grade where gid=st.gid ) as gname  "  + sql;
		     
		    map=commonDao.queryPage(newsql, "select count(*) " + sql_first, page, page_size, new Object[] { sid }, new Object[] { sid }, new SchoolTeacherRowMapper(SchoolTeacherRowMapper.AND_NEW_STEACHER));
		}
		return map;
	}
}
